﻿using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class CityDAL
    {
        private const string SQL_SELECT_WS_CITY = "select CIT_CITY_ID as CITY_ID,CIT_CITY_NAME as CITY_NAME,CIT_NATION_ID as NATION_ID,CIT_SORT_INDEX as SORT_INDEX,CIT_AIRPORT_NAME as AIRPORT_NAME,CIT_AIRPORT_CODE_3 as AIRPORT_CODE_3,CIT_AIRPORT_CODE_4 as AIRPORT_CODE_4,CIT_CHINESE_SPELL as CHINESE_SPELL,CIT_ENGLISH_NAME as ENGLISH_NAME,CIT_RCRE_STAFF_ID as RCRE_STAFF_ID,CIT_RCRE_TIME as RCRE_TIME,CIT_LCHG_STAFF_ID as LCHG_STAFF_ID,CIT_LCHG_TIME as LCHG_TIME,(select NAT_NATION_NAME from WS_NATION where NAT_NATION_ID=CIT_NATION_ID) as NATION_NAME from WS_CITY where 1=1";
        private const string SQL_INSERT_WS_CITY = "insert into WS_CITY(CIT_CITY_ID,CIT_CITY_NAME,CIT_NATION_ID,CIT_CHINESE_SPELL,CIT_AIRPORT_NAME,CIT_AIRPORT_CODE_3,CIT_AIRPORT_CODE_4,CIT_ENGLISH_NAME,CIT_SORT_INDEX,CIT_RCRE_STAFF_ID,CIT_RCRE_TIME) values(@CityId,@CityName,@NationId,@ChineseSpell,@AirportName,@AirportCode3,@AirportCode4,@EnglishName,@SortIndex,@LastStaffId,@LastTime)";
        private const string SQL_UPDATE_WS_CITY = "update WS_CITY set CIT_CITY_NAME=@CityName,CIT_CHINESE_SPELL=@ChineseSpell,CIT_AIRPORT_NAME=@AirportName,CIT_AIRPORT_CODE_3=@AirportCode3,CIT_AIRPORT_CODE_4=@AirportCode4,CIT_ENGLISH_NAME=@EnglishName,CIT_SORT_INDEX=@SortIndex,CIT_LCHG_STAFF_ID=@LastStaffId,CIT_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_DELETE_WS_CITY = "delete from WS_CITY where 1=1";
        private const string SQL_COUNT_WS_CITY = "select count(CIT_CITY_ID) from WS_CITY where 1=1";

        private const string SQL_WHERE_CITY_ID = " and CIT_CITY_ID=@CityId ";
        private const string SQL_WHERE_CITY_ID_NAME = " and CIT_CITY_ID<>@CityId and CIT_CITY_NAME=@CityName ";
        private const string SQL_WHERE_NATION_ID = " and CIT_NATION_ID=@NationId ";

        private const string SQL_ORDER_BY_SORT_INDEX = " order by NATION_ID,CITY_ID,SORT_INDEX ";

        private const string PARM_CITY_ID = "@CityId";
        private const string PARM_CITY_NAME = "@CityName";
        private const string PARM_NATION_ID = "@NationId";
        private const string PARM_CHINESE_SPELL = "@ChineseSpell";
        private const string PARM_AIRPORT_NAME = "@AirportName";
        private const string PARM_AIRPORT_CODE_3 = "@AirportCode3";
        private const string PARM_AIRPORT_CODE_4 = "@AirportCode4";
        private const string PARM_ENGLISH_NAME = "@EnglishName";
        private const string PARM_SORT_INDEX = "@SortIndex";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";

        private static CityDAL instance = new CityDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static CityDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet()
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_CITY);
            sbSQL.Append(SQL_ORDER_BY_SORT_INDEX);

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), null);
        }
        public int Insert(DbTransaction trans, CityModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_CITY_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_NATION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.NationId });
            parms.Add(new MySqlParameter(PARM_CHINESE_SPELL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ChineseSpell });
            parms.Add(new MySqlParameter(PARM_AIRPORT_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirportName });
            parms.Add(new MySqlParameter(PARM_AIRPORT_CODE_3, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirportCode3 });
            parms.Add(new MySqlParameter(PARM_AIRPORT_CODE_4, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirportCode4 });
            parms.Add(new MySqlParameter(PARM_ENGLISH_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.EnglishName });
            parms.Add(new MySqlParameter(PARM_SORT_INDEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.SortIndex });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_CITY, parms.ToArray());
        }
        public int Update(DbTransaction trans, CityModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_CITY);
            sbSQL.Append(SQL_WHERE_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_CITY_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_NATION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.NationId });
            parms.Add(new MySqlParameter(PARM_CHINESE_SPELL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ChineseSpell });
            parms.Add(new MySqlParameter(PARM_AIRPORT_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirportName });
            parms.Add(new MySqlParameter(PARM_AIRPORT_CODE_3, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirportCode3 });
            parms.Add(new MySqlParameter(PARM_AIRPORT_CODE_4, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirportCode4 });
            parms.Add(new MySqlParameter(PARM_ENGLISH_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.EnglishName });
            parms.Add(new MySqlParameter(PARM_SORT_INDEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.SortIndex });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_CITY);
            sbSQL.Append(SQL_WHERE_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CITY);
            sbSQL.Append(SQL_WHERE_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(trans, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CITY);
            sbSQL.Append(SQL_WHERE_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id, string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CITY);
            sbSQL.Append(SQL_WHERE_CITY_ID_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_CITY_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool NationUsing(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CITY);
            sbSQL.Append(SQL_WHERE_NATION_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_NATION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
